﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;

namespace BawangMemo.BackendModels
{
    public class ListRepository
    {
        public static List<WordWrapper> GetList(CourseWrapper cw, int listId)
        {
            string courseName = cw.CourseName;
            var result = new List<WordWrapper>();

            using (OleDbConnection connection = new OleDbConnection(Constants.ConnectionString))
            {
                connection.Open();
                var reader = DataAccess.ExecuteReader(connection,
                    String.Format(@"SELECT StartId, EndId FROM {0}_lst WHERE ListId={1}", courseName, listId));
                while (reader.Read())
                {
                    int start = reader.GetInt32(0);
                    int end = reader.GetInt32(1);
                    for (int id = start; id <= end; id++)
                    {
                        result.Add(cw.WordList[id - 1]);
                    }
                }
            }

            return result;
        }

        public static int CreateCourseListTable(CourseWrapper cw, int listSize)
        {
            
            var wordList = cw.WordList;
            if (wordList.Count > 0)
            {
                return CreateCourseListTable(cw.CourseName, 1, wordList.Count, listSize);
            }
            else
            {
                return -1;
            }
        }

        public static int CreateCourseListTable(string courseName, int startId, int endId, int listSize)
        {
            using (OleDbConnection connection = new OleDbConnection(Constants.ConnectionString))
            {
                connection.Open();
                try
                {
                    DataAccess.ExecuteNonQuery(connection,
                        "CREATE TABLE " + courseName + "_lst " +
                        "(ListId int not null, StartId int not null, EndId int not null, primary key (ListId))");

                    int i = startId, j, listId = 0;
                    while (i <= endId)
                    {
                        if (i + listSize - 1 > endId)
                        {
                            j = endId;
                        }
                        else { j = i + listSize - 1; }
                        DataAccess.ExecuteNonQuery(connection,
                            "INSERT INTO " + courseName +
                            "_lst (ListId, StartId, EndId) values " +
                            String.Format("({0},{1},{2})", ++listId, i, j));
                        i = j + 1;
                    }
                }
                catch
                {
                    return -1;
                }
                finally { connection.Close(); }
            }
            return 0;
        }

        public static int GetListCount(CourseWrapper course)
        {
            int result = 0;
            using (OleDbConnection connection = new OleDbConnection(Constants.ConnectionString))
            {
                connection.Open();
                var reader = DataAccess.ExecuteReader(connection,
                    String.Format(@"SELECT COUNT(*) FROM {0}_lst", course.CourseName));
                while (reader.Read())
                {
                    result = reader.GetInt32(0);
                }
            }
            return result;
        }

        public static ListDataModel GetListData(CourseWrapper course, int i)
        {
            ListDataModel result = null;

            using (OleDbConnection connection = new OleDbConnection(Constants.ConnectionString))
            {
                connection.Open();
                var reader = DataAccess.ExecuteReader(connection,
                    String.Format(@"SELECT StartId, EndId FROM {0}_lst WHERE ListId={1}", course.CourseName, i));
                while (reader.Read())
                {
                    int startId = reader.GetInt32(0);
                    int endId = reader.GetInt32(1);
                    result = new ListDataModel
                    {
                        ListName = "List " + i,
                        ListId = i,
                        ListCount = (endId - startId + 1).ToString(),
                        ListFirst = course.WordList[startId - 1].Spell,
                    };
                }
            }

            return result;
        }

        public static List<ListDataModel> GetAllListData(CourseWrapper course)
        {
            var result = new List<ListDataModel>();
            using (OleDbConnection connection = new OleDbConnection(Constants.ConnectionString))
            {
                connection.Open();
                var reader = DataAccess.ExecuteReader(connection,
                    String.Format(@"SELECT ListId, StartId, EndId FROM {0}_lst", course.CourseName));
                while (reader.Read())
                {
                    int listId = reader.GetInt32(0);
                    int startId = reader.GetInt32(1);
                    int endId = reader.GetInt32(2);
                    result.Add(new ListDataModel
                    {
                        ListName = "List " + listId,
                        ListId = listId,
                        ListCount = (endId - startId + 1).ToString(),
                        ListFirst = course.WordList[startId - 1].Spell,
                    });
                }
            }
            return result;
        }

    }
}
